package Database;

import java.sql.*;
import java.util.ArrayList;

import Model.Appointment;
import Model.Approval;
import Model.Committee;

public class CommitteeDB {

	public static void createCommittee(Committee committee) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "INSERT INTO committee(Committee_ID, Committee_Title, Committee_Purpose, Committee_RolesResponsibility, Committee_FrequencyOfMeeting, Committee_Date, Committee_CreatedBy) VALUES(?, ?, ?, ?, ?, ?, ?)";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, committee.getId());
			stmt.setString(2, committee.getTitle());
			stmt.setString(3, committee.getPurpose());
			stmt.setString(4, committee.getRolesResponsibility());
			stmt.setString(5, committee.getFrequencyOfMeeting());
			stmt.setString(6, committee.getDate());
			stmt.setString(7, committee.getCreatedBy());

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	
	
	public static void updateStatus(Committee app) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "UPDATE committee SET Committee_Status = ?, Committee_ApprovedBy = ? WHERE Committee_ID = ?";

		try {
			stmt = con.prepareStatement(select);
			stmt.setInt(1, app.getStatus());
			stmt.setString(2, app.getApprovedBy());
			stmt.setString(3, app.getId());
			System.out.println(stmt.toString());
			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}
	
	
	
	public static void updateCommittee(Committee committee) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "UPDATE committee SET Committee_Title = ?, Committee_Purpose = ?, Committee_RolesResponsibility = ?, Committee_FrequencyOfMeeting = ?, Committee_Date = ? WHERE Committee_ID = ?";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, committee.getTitle());
			stmt.setString(2, committee.getPurpose());
			stmt.setString(3, committee.getRolesResponsibility());
			stmt.setString(4, committee.getFrequencyOfMeeting());
			stmt.setString(5, committee.getDate());
			stmt.setString(6, committee.getId());

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static void deleteCommittee(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		String select = "DELETE FROM committee WHERE Committee_ID = ?";

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);

			stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static ArrayList<Committee> getAllCommittees() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM committee  where Committee_Status = 2 ORDER BY Committee_Title ;";

		ArrayList<Committee> committeeArr = new ArrayList<Committee>();
		Committee committee = null;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, credID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				committee = new Committee();
				committee.setId(rs.getString("Committee_ID"));
				committee.setTitle(rs.getString("Committee_Title"));
				committee.setPurpose(rs.getString("Committee_Purpose"));
				committee.setRolesResponsibility(rs
						.getString("Committee_RolesResponsibility"));
				committee.setFrequencyOfMeeting(rs
						.getString("Committee_FrequencyofMeeting"));
				committee.setDate(rs.getString("Committee_Date"));
				committee.setCreatedBy(rs.getString("Committee_CreatedBy"));

				committeeArr.add(committee);
			}
		} catch (Exception e) {
			e.printStackTrace();
			committee = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return committeeArr;
	}

	
	public static ArrayList<Committee> getAllCommitteesUnApproved() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM committee  where Committee_Status < 0 ORDER BY Committee_Title ;";

		ArrayList<Committee> committeeArr = new ArrayList<Committee>();
		Committee committee = null;

		try {
			stmt = con.prepareStatement(select);
			// stmt.setString(1, credID);
			rs = stmt.executeQuery();
			while (rs.next()) {
				committee = new Committee();
				committee.setId(rs.getString("Committee_ID"));
				committee.setTitle(rs.getString("Committee_Title"));
				committee.setPurpose(rs.getString("Committee_Purpose"));
				committee.setRolesResponsibility(rs
						.getString("Committee_RolesResponsibility"));
				committee.setFrequencyOfMeeting(rs
						.getString("Committee_FrequencyofMeeting"));
				committee.setDate(rs.getString("Committee_Date"));
				committee.setStatus(rs.getInt("Committee_Status"));
				committee.setCreatedBy(rs.getString("Committee_CreatedBy"));
				committeeArr.add(committee);
			}
		} catch (Exception e) {
			e.printStackTrace();
			committee = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return committeeArr;
	}
	
	public static Committee getNewCommitteebyID(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM committee WHERE Committee_ID = ? ";
		Committee committee = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				
				committee = new Committee();
				committee.setId(rs.getString("Committee_ID"));
				committee.setTitle(rs.getString("Committee_Title"));
				committee.setPurpose(rs.getString("Committee_Purpose"));
				committee.setRolesResponsibility(rs
						.getString("Committee_RolesResponsibility"));
				committee.setFrequencyOfMeeting(rs
						.getString("Committee_FrequencyofMeeting"));
				committee.setDate(rs.getString("Committee_Date"));
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			committee = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return committee;
	}
	
	public static Committee getCommitteebyID(String id) {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT * FROM committee WHERE Committee_ID = ?";
		Committee committee = null;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				
				committee = new Committee();
				committee.setId(rs.getString("Committee_ID"));
				committee.setTitle(rs.getString("Committee_Title"));
				committee.setPurpose(rs.getString("Committee_Purpose"));
				committee.setRolesResponsibility(rs
						.getString("Committee_RolesResponsibility"));
				committee.setFrequencyOfMeeting(rs
						.getString("Committee_FrequencyofMeeting"));
				committee.setDate(rs.getString("Committee_Date"));
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			committee = null;
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return committee;
	}

	public static int getLastNumber() {
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT Committee_ID FROM qmsdb.committee ORDER BY cast(Committee_ID  as unsigned) DESC limit 1";

		int lastNumber = 0;

		try {
			stmt = con.prepareStatement(select);
			rs = stmt.executeQuery();
			while (rs.next()) {
				lastNumber = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		return lastNumber;
	}

	public static boolean titleExists(String title) {
		// Returns true if name already exists, returns false if name does not
		// exist yet.
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT COUNT(*) AS Number FROM committee WHERE Committee_Title=?";

		int numberOfNames = -1;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, title);
			rs = stmt.executeQuery();
			while (rs.next()) {
				numberOfNames = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		if (numberOfNames > 0)
			return true;
		else
			return false;
	}

	public static boolean idExists(String id) {
		// Returns true if id already exists, returns false if id does not exist
		// yet.
		Connection con = DBConnection.connect(true);
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String select = "SELECT COUNT(*) AS Number FROM committee WHERE Committee_ID=?";

		int numberOfNames = -1;

		try {
			stmt = con.prepareStatement(select);
			stmt.setString(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				numberOfNames = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}

		if (numberOfNames > 0)
			return true;
		else
			return false;
	}
}
